package com.CardBudgetBook.DataBase;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.CardBudgetBook.Common.CommonUsage;

public class BudgetDB extends SQLiteOpenHelper {
	private static final String DB_NAME = "CardBudgetBook.db";
	private static final int VERSION = 1;

	private static final String BUDGET_TABLE = "budget";
	private static final String DATE = "date";
	private static final String TIME = "time";
	private static final String COMPANY = "company";
	private static final String LOCATION = "location";
	private static final String OUTLAY = "money";

	private SQLiteDatabase db;

	public BudgetDB(Context context) {
		super(context, DB_NAME, null, VERSION);
		// TODO Auto-generated constructor stub
		db = this.getWritableDatabase();
	}

	public boolean isOpen() {
		return db.isOpen();
	}

	@Override
	public synchronized void close() {
		db.close();
		super.close();
	}

	@Override
	protected void finalize() throws Throwable {
		close();
		super.finalize();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("CREATE TABLE " + BUDGET_TABLE + "(" + DATE + " TEXT, "
				+ TIME + " TEXT, " + COMPANY + " TEXT, " + LOCATION + " TEXT, "
				+ OUTLAY + " TEXT)");
	}

	public void test() {
		Cursor c = db.query(BUDGET_TABLE, null, null, null, null, null,
				null);
		Log.d("Database", "TEST");

		// DB가 비어있으면 추가
		if (c.getCount() == 0) {

			addList(CommonUsage.getToday(), CommonUsage.getCurrentTime(),
					"국민카드", "패밀리마트", 3800);
			addList(CommonUsage.getToday(), CommonUsage.getCurrentTime(),
					"신한카드", "롯데마트", 4000);
			addList(CommonUsage.getToday(), CommonUsage.getCurrentTime(),
					"롯데카드", "우리집", 30);

			addList("2012.01.20", "12:30:11", "국민카드", "너네마트", 300);
			addList("2012.01.20", "13:31:21", "신한카드", "우리마트", 3400);

			addList("2012.01.21", "15:10:21", "롯데카드", "맛집", 30215);
			addList("2012.01.21", "17:31:22", "삼성카드", "멋집", 38100);

			addList("2012.01.22", "02:27:11", "너거카드", "고양이집", 300000);
			addList("2012.01.22", "06:35:00", "내카드", "원숭이집", 34000);

			addList("2012.01.23", "15:01:37", "포인트카드", "광안리", 31500);
			addList("2012.01.23", "23:03:48", "에그카드", "해운대", 34500);

			addList("2012.01.24", "03:03:23", "LG카드", "칼빈대", 1300);
			addList("2012.01.24", "22:22:01", "부경카드", "부경대", 34100);

			addList("2012.01.25", "23:23:59", "푸쿙카드", "구도", 1000);
			addList("2012.01.25", "18:44:38", "티몬카드", "신학", 553400);

		}
		c.close();
	}

	public BudgetInfo findByDate(String date) {
		Cursor c = db.query(BUDGET_TABLE, null, DATE + " = '" + date + "'",
				null, null, null, null);

		if (c.moveToFirst()) {
			final int indexDATE = c.getColumnIndex(DATE);
			final int indexTIME = c.getColumnIndex(TIME);
			final int indexCOMPANY = c.getColumnIndex(COMPANY);
			final int indexLOCATION = c.getColumnIndex(LOCATION);
			final int indexOUTLAY = c.getColumnIndex(OUTLAY);

			do {
				String resDate = c.getString(indexDATE);
				String resTime = c.getString(indexTIME);
				String resCompany = c.getString(indexCOMPANY);
				String resLocation = c.getString(indexLOCATION);
				int resOutlay = c.getInt(indexOUTLAY);

				if (resDate.equals("") && resCompany.equals(date)) {
					return new BudgetInfo(resDate, resTime, resCompany,
							resLocation, resOutlay);
				}

			} while (c.moveToNext());
		}
		c.close();
		return null;

	}

	public BudgetInfo findByCompany(String company) {
		Cursor c = db.query(BUDGET_TABLE, null, COMPANY + " = '" + company
				+ "'", null, null, null, null);

		if (c.moveToFirst()) {
			final int indexDATE = c.getColumnIndex(DATE);
			final int indexTIME = c.getColumnIndex(TIME);
			final int indexCOMPANY = c.getColumnIndex(COMPANY);
			final int indexLOCATION = c.getColumnIndex(LOCATION);
			final int indexOUTLAY = c.getColumnIndex(OUTLAY);

			do {
				String resDate = c.getString(indexDATE);
				String resTime = c.getString(indexTIME);
				String resCompany = c.getString(indexCOMPANY);
				String resLocation = c.getString(indexLOCATION);
				int resOutlay = c.getInt(indexOUTLAY);

				if (resDate.equals("") && resCompany.equals(company)) {
					return new BudgetInfo(resDate, resTime, resCompany,
							resLocation, resOutlay);
				}

			} while (c.moveToNext());
		}
		c.close();
		return null;

	}

	public ArrayList<BudgetInfo> getAll() {
		ArrayList<BudgetInfo> budget = new ArrayList<BudgetInfo>();
		// Query를 Like문을 사용해서 써야됨
		Cursor c = db.query(BUDGET_TABLE, null, null, null, null, null, DATE
				+ " ASC"); // DESC
		extract(budget, c);
		return budget;
	}

	protected void extract(ArrayList<BudgetInfo> budget, Cursor c) {
		if (c.moveToFirst()) {
			final int indexDATE = c.getColumnIndex(DATE);
			final int indexTIME = c.getColumnIndex(TIME);
			final int indexCOMPANY = c.getColumnIndex(COMPANY);
			final int indexLOCATION = c.getColumnIndex(LOCATION);
			final int indexOUTLAY = c.getColumnIndex(OUTLAY);

			do {
				String resDate = c.getString(indexDATE);
				String resTime = c.getString(indexTIME);
				String resCompany = c.getString(indexCOMPANY);
				String resLocation = c.getString(indexLOCATION);
				int resOutlay = c.getInt(indexOUTLAY);

				budget.add(new BudgetInfo(resDate, resTime, resCompany,
						resLocation, resOutlay));

			} while (c.moveToNext());

		}
		c.close();
	}

	// synchronized public void delRun(String dir){
	// delList(RUNNING_TABLE, dir);
	// }
	//
	//
	// synchronized private void delList(String tbl, String dir){
	// db.delete(tbl, DIR + " = '" + dir + "'", null);
	//
	// }

	public void addList(String date, String time, String company,
			String location, int outlay) {
		ContentValues cv = new ContentValues();
		cv.put(DATE, date);
		cv.put(TIME, time);
		cv.put(COMPANY, company);
		cv.put(LOCATION, location);
		cv.put(OUTLAY, outlay);
		db.insert(BUDGET_TABLE, null, cv);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + BUDGET_TABLE);
		onCreate(db);
	}

}
